#!/bin/perl






# Transform the csv files to map team names.  Needs to be done one time only
my $mapTeamNames = 1;
if ($mapTeamNames)
{
   #system("nameTransform.pl regressionOHDBC3K2013.csv");
   system("nameTransform.pl regressionMilton2013.csv");
   system("nameTransform.pl regressionPickering2013.csv");
   #system("nameTransform.pl regressionParrySound2013.csv");
   system("nameTransform.pl regressionCenterIsland2013.csv");
   system("nameTransform.pl regressionNationals2013.csv");
   system("nameTransform.pl regressionOttawa2013.csv");
   #system("nameTransform.pl regressionWelland2013.csv");
   #system("nameTransform.pl regressionLachine2013.csv");
   system("nameTransform.pl regressionMontrealChallenge2013.csv");
   system("nameTransform.pl regressionHamilton2013.csv");
   system("nameTransform.pl regressionTorontoCup2013.csv");
   system("nameTransform.pl regressionWoodstock2013.csv");
   system("nameTransform.pl regressionGWNSport2013.csv");
   system("nameTransform.pl regressionGWNC2013.csv");
   system("nameTransform.pl regressionQuebecCup2013.csv");
}

# Careful with opening CSV files in excel.  It will change the format of my times to mm:ss.0 so I need
# to change it back to mm:ss.00 before saving or else I will lose the 1/100 of a seconds column
#system("processCSV.pl regressionOHDBC3K2012.csv");

system("processCSV.pl regressionMilton2013.csv");
system("processCSV.pl regressionPickering2013.csv");
#system("processCSV.pl regressionParrySound2013.csv");
system("processCSV.pl regressionCenterIsland2013.csv");
system("processCSV.pl regressionNationals2013.csv");
system("processCSV.pl regressionOttawa2013.csv");
#system("processCSV.pl regressionWelland2013.csv");
#system("processCSV.pl regressionLachine2013.csv");
system("processCSV.pl regressionMontrealChallenge2013.csv");
system("processCSV.pl regressionHamilton2013.csv");
system("processCSV.pl regressionWoodstock2013.csv");
system("processCSV.pl regressionTorontoCup2013.csv");
system("processCSV.pl regressionGWNSport2013.csv");
system("processCSV.pl regressionGWNC2013.csv");
system("processCSV.pl regressionQuebecCup2013.csv");


system("db2 connect to dboat");
system("db2 drop table race");
system("db2 create table race (TEAM VARCHAR(100), " .
                              "CATEGORY VARCHAR(100), " .
                              "TIME DECIMAL(31,20), " .
                              "RACENO INTEGER, " .
                              "DISTANCE INTEGER, " .
                              "REGATTA VARCHAR(25), " .
                              "YEAR INTEGER, " .
                              "LANE INTEGER, " .
                              "RACENAME VARCHAR(50) WITH DEFAULT)");

#system("db2 load from regressionOHDBC3K2013.csv.out of del insert into race");
system("db2 load from regressionMilton2013.csv.out of del insert into race");
system("db2 load from regressionPickering2013.csv.out of del insert into race");
system("db2 load from regressionCenterIsland2013.csv.out of del insert into race");
system("db2 load from regressionNationals2013.csv.out of del insert into race");
system("db2 load from regressionOttawa2013.csv.out of del insert into race");
#system("db2 load from regressionWelland2013.csv.out of del insert into race");
#system("db2 load from regressionParrySound2013.csv.out of del insert into race");
#system("db2 load from regressionLachine2013.csv.out of del insert into race");
system("db2 load from regressionMontrealChallenge2013.csv.out of del insert into race");
system("db2 load from regressionHamilton2013.csv.out of del insert into race");
system("db2 load from regressionWoodstock2013.csv.out of del insert into race");
system("db2 load from regressionTorontoCup2013.csv.out of del insert into race");
system("db2 load from regressionGWNSport2013.csv.out of del insert into race");
system("db2 load from regressionGWNC2013.csv.out of del insert into race");
system("db2 load from regressionQuebecCup2013.csv.out of del insert into race");

# Add a column to allow us to exclude certain races from influencing regression
# line.  This is needed only if a really wonky result for a team is skewing the regression
# and we wish to keep the team in the rankings without renaming it but not allow the race
# to influence the regression line
system("db2 alter table race add exclude_flag integer default 0");
system("db2 UPDATE race set exclude_flag=1 WHERE team='SWORDFISH' and REGATTA='CenterIsland' and RACENO IN (10,24)");
system("db2 UPDATE race set exclude_flag=1 WHERE team='SWORDFISH' and REGATTA='GWN Sport'");
system("db2 UPDATE race set exclude_flag=1 WHERE team='SHOCKWAVE' and REGATTA='GWN Sport'");
system("db2 UPDATE race set exclude_flag=1 WHERE team='AGI DRAGON MASTERS' and REGATTA='GWN Sport'");




system("db2 drop table regattas");
system("db2 create table regattas (NAME VARCHAR(25), REGRESS_ORDER INTEGER, PRINT_ORDER INTEGER, WHEN DATE, WEBSITE VARCHAR(100), COMPETITIVE DECIMAL(4,2))");
# ---------------------- 500m regression order ----------------------
# -1 for regress_order means it will not be included in the regression
system("db2 insert into regattas values " .
            " ('Toronto Cup',     -1, -1,  DATE('2013-08-10'), 'http://www.sunnysidepc.ca/toronto-cup', 0), " . 
#            " ('Welland',      3,  2,  DATE('2012-06-08'), 'http://www.dragon-boats.com/welland/', 0), " .
            " ('CenterIsland', 0,  2,  DATE('2013-06-22'), 'http://www.dragonboats.com/', 0), " . 
            " ('Milton',       1,  0,  DATE('2013-05-25'), 'http://www.dragon-boats.com/milton/', 0),  " . 
#            " ('Parry Sound',  -1,  -1,  DATE('2012-06-16'), 'http://www.dragonboatfestival.org/', 0),  " .
            " ('Pickering',    2,  1,  DATE('2013-06-01'), 'http://www.pdbc.ca/', 0),  " .  
            " ('Hamilton',     3,  3,  DATE('2013-07-06'), 'http://www.lively-dragon.com/', 0), " .
            " ('Montreal Challenge', 4,  4,  DATE('2013-07-13'), 'http://montrealchallenge.22dragons.com/', 0),  " .
            " ('GWN Sport',    5,  5,  DATE('2013-07-20'), 'http://www.gwndragonboat.com/', 0),  " .
            " ('Woodstock',    6,  6,  DATE('2013-08-10'), 'http://www.woodstockdragonboat.ca/', 0), " .
            " ('Nationals',    7, 7,  DATE('2013-08-23'), 'http://www.dragonboat.ca', 0), " .
            " ('GWNC',         8,  8,  DATE('2013-09-07'), 'http://www.gwndragonboat.com/', 0),  " .
            " ('Quebec Cup',   9, 9,  DATE('2013-09-15'), 'http://22dragons.com/', 0), " . 
            " ('Ottawa',       -1,  -1,  DATE('2013-06-22'), 'http://www.dragonboat.net/', 0) " 
      ); 





# ---------------------- 200m regression order ----------------------
#system("db2 insert into regattas values ('Pickering', 0, 0, 1.0), ('GWN Sport', 1, 2, 1.0), ('Montreal', 2, 3, 1.0), ('Ottawa', 3, 1, 1.0), ('Lachine', 4, 4, 1.0), ('Belleville', 5, 5, 1.0)"); 


# ---------------------- 2000m regression order ----------------------
#system("db2 insert into regattas values ('Pickering', 0, 0, 1.0), ('GWN Sport', 1, 1, 1.0), ('Woodstock', 2, 2, 1.0), ('Milton', 3, 3, 1.0), ('Hamilton', 4, 4, 1.0),  ('Belleville', 5, 5, 1.0)");


# export files to load to database on website.  Note that sometimes the results just list times without
# showing what heat the teams were in.  In these cases we make up imaginary raceNo -666
# but we don't want these in the website archive.  They are only useful for the regression
system("db2 export to race2013.del of del select TEAM, CATEGORY, TIME, RACENO, DISTANCE, REGATTA, YEAR, LANE, RACENAME from race where RACENO != -666");
system("db2 export to dates2013.del of del select distinct(race.regatta), regattas.regress_order, regattas.print_order, regattas.when, regattas.website, regattas.competitive from race, regattas where race.regatta=regattas.name");



system("db2 connect");
system("db2 delete from race where team like 'WATERLOO WARRIORS 3' and REGATTA='Pickering' and RACENO=44");
system("db2 delete from race where team like 'MOFOS' and REGATTA='GWNC' and RACENO=48");

#system("db2 update race set team='MOFOS' where team='PHO KING WARRIORS'");
#system("db2 update race set TIME=128.07 where team='CSDC' and REGATTA='Pickering' and RACENO=14");
#system("db2 update race set team='NETWORK 54' where team='HYDROPHALLIC DRAGONS'");
#system("db2 delete from race where team like 'OHDBC DRIFTERS'");
#system("db2 delete from race where team like 'NDC REP'");
#system("db2 delete from race where team like 'SRP' and regatta like 'NDC' and raceNo=33");
#system("db2 delete from race where team like '22 DRAGONS A'");
#system("db2 delete from race where team like '22 DRAGONS B'");


print("Check for same team names with more than one gender classification: \n");
system("db2 select distinct team from race where category='Mixed' INTERSECT SELECT distinct team FROM race WHERE not category='Mixed'");

print("Check for questionable 500m times: \n");
system("db2 \"select * from race where distance=500 and (time<90 or time>240)\"");

print("Check for unknown categories: \n");
system("db2 \"select * from race where category not in ('WOMEN', 'Women', 'OPEN', 'Open', 'MIXED', 'Mixed')\" ");



system("db2 connect");
system("db2 connect reset");

